﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace ECSS.Winform
{
    public class ExamineECSS
    {
        private SqlConnection sqlconn;
        private SqlConnection sqlconn2;
        private SqlCommand sqlcmd;
        private SqlCommand sqlcmd2;
        private SqlDataReader sqlrd;
        private SqlDataReader sqlrd2;


        private string sql;
        private List<string> exceptionOrders;

        public List<string> GetOrderIdAndOfferingId()
        {
            exceptionOrders = new List<string>();

            sql = "select orderid from orderinfo";

            sqlconn = new SqlConnection();
            sqlconn.ConnectionString = ConfigurationManager.ConnectionStrings["ECSSDb"].ConnectionString;

            sqlcmd = new SqlCommand();
            sqlcmd.Connection = sqlconn;
            sqlcmd.CommandText = sql;

            try
            {
                sqlcmd.Connection.Open();
                sqlrd = sqlcmd.ExecuteReader();
                while (sqlrd.Read())
                {
                    if (IsExcpetionOrderId(sqlrd["orderid"].ToString()))
                    { 
                        exceptionOrders.Add(sqlrd["orderid"].ToString());
                    }
                }
            }
            finally
            {
                sqlcmd.Connection.Close();
            }
            
            return exceptionOrders;
        }

        public bool IsExcpetionOrderId(string orderId)
        {
            sql = @"select dbo.checkOrderService(@orderid)";

            sqlconn2 = new SqlConnection();
            sqlconn2.ConnectionString = ConfigurationManager.ConnectionStrings["ECSSDb"].ConnectionString;

            sqlcmd2 = new SqlCommand();
            sqlcmd2.Connection = sqlconn2;
            sqlcmd2.CommandText = sql;

            sqlcmd2.Parameters.Add("@orderid", SqlDbType.NVarChar, 20);
            sqlcmd2.Parameters["@orderid"].Value = orderId;

            try
            {
                sqlcmd2.Connection.Open();
                sqlrd2 = sqlcmd2.ExecuteReader();

                if (sqlrd2.HasRows)
                {
                    sqlrd.Read();
                    if (sqlrd[0].ToString().Equals(string.Empty))
                    {
                        return false;
                    }
                    else
                    {
                        return true;
                    }                    
                }
                else
                {
                    return false;
                }
            }
            finally
            {
                sqlcmd2.Connection.Close();
            }
        }
    }
}
